package com.zzxz.practicaltrainingsys.mapper;

import com.zzxz.practicaltrainingsys.vo.ViewStudent;
import com.zzxz.practicaltrainingsys.entity.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface StudentMapper {
    @Select("select * from student")
    List<Student> getStudent();

    @Select("select distinct student_name from student")
    List<String> getStudentNames();

    // 动态条件查询
    @Select({
            "<script>",
            "SELECT s.student_id, s.student_name, s.student_image, s.student_sex, s.student_password, s.student_phone, s.student_email, s.student_idcard, ",
            "sc.school_id, sc.school_name, c.college_id, c.college_name, m.major_id, m.major_name, g.grade_id, g.grade_name, ",
            "s.student_state ", // 一定要加上这一行
            "FROM student s ",
            "LEFT JOIN school sc ON s.school_id = sc.school_id ",
            "LEFT JOIN college c ON s.college_id = c.college_id ",
            "LEFT JOIN major m ON s.major_id = m.major_id ",
            "LEFT JOIN grade g ON s.grade_id = g.grade_id ",
            "<where>",
            "  <if test='school_id != null'> AND s.school_id = #{school_id} </if>",
            "  <if test='college_id != null'> AND s.college_id = #{college_id} </if>",
            "  <if test='major_id != null'> AND s.major_id = #{major_id} </if>",
            "  <if test='grade_id != null'> AND s.grade_id = #{grade_id} </if>",
            "</where>",
            "</script>"
    })
    List<ViewStudent> findStudentByCondition(
            @Param("school_id") Integer school_id,
            @Param("college_id") Integer college_id,
            @Param("major_id") Integer major_id,
            @Param("grade_id") Integer grade_id
    );


    @Update("UPDATE student SET student_password = #{student_password} WHERE student_id = #{student_id}")
    int updatePassword(@Param("student_id") Integer student_id,
                       @Param("student_password") String student_password);


    @Select("SELECT * FROM student WHERE student_id = #{student_id}")
    Student getStudentById(@Param("student_id") Integer student_id);


    @Update("UPDATE student SET " +
            "student_name = #{student_name}, " +
            "student_image = #{student_image}, " +
            "student_sex = #{student_sex}, " +
            "school_id = #{school_id}, " +
            "college_id = #{college_id}, " +
            "major_id = #{major_id}, " +
            "grade_id = #{grade_id}, " +
            "student_password = #{student_password}, " +
            "student_phone = #{student_phone}, " +
            "student_email = #{student_email}, " +
            "student_idcard = #{student_idcard} " +
            "WHERE student_id = #{student_id}")
    int updateStudent(Student student);

    //新增学生信息
    @Insert("INSERT INTO student (student_id, student_name, student_image, student_sex, school_id, college_id, major_id, grade_id, student_password, student_phone, student_email, student_idcard) " +
            "VALUES (#{student_id}, #{student_name}, #{student_image}, #{student_sex}, #{school_id}, #{college_id}, #{major_id}, #{grade_id}, #{student_password}, #{student_phone}, #{student_email}, #{student_idcard})")
    int addStudent(Student student);

    //五表联查
    @Select("SELECT s.student_id, s.student_name, s.student_image, s.student_sex, s.student_password, s.student_phone, s.student_email, s.student_idcard, " +
            "sc.school_id, sc.school_name, c.college_id, c.college_name, m.major_id, m.major_name, g.grade_id, g.grade_name " +
            "FROM student s " +
            "LEFT JOIN school sc ON s.school_id = sc.school_id " +
            "LEFT JOIN college c ON s.college_id = c.college_id " +
            "LEFT JOIN major m ON s.major_id = m.major_id " +
            "LEFT JOIN grade g ON s.grade_id = g.grade_id " +
            "WHERE s.student_id = #{student_id}")
    ViewStudent findViewStudentById(@Param("student_id") Integer student_id);

    @Update("UPDATE student SET student_state = 1 WHERE student_id = #{student_id}")
    int logicDeleteStudent(@Param("student_id") Integer student_id);

    @Update({
            "<script>",
            "UPDATE student SET student_state = 1 WHERE student_id IN",
            "<foreach collection='list' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    int logicDeleteStudents(List<Integer> ids);

    @Select("SELECT COUNT(student_id) AS total_students FROM student;")
    int getTotalStudentsNum();

    //根据电话查询学生信息
    @Select("SELECT * FROM student WHERE student_phone = #{student_phone}")
    Student findStudentByPhone(@Param("student_phone") String student_phone);

    // 通过项目ID查找所有相关学生
    @Select("SELECT DISTINCT s.* " +
            "FROM project p " +
            "JOIN team t ON t.project_id = p.project_id " +
            "JOIN member m ON m.team_id = t.team_id " +
            "JOIN student s ON s.student_id = m.student_id " +
            "WHERE p.project_id = #{projectId}")
    List<Student> getStudentsByProjectId(@Param("projectId") Integer projectId);
}